#!/usr/bin/python
# -*- coding: utf-8 -*-
import cx_Oracle
import sys
import urllib
import os


# function #
def conndb(dbname='TESTDB', username='TEST2', pwd='11111'):
    ##创建数据库连接，设置默认值
    if dbname == 'TESTDB':
        username = "TEST2"
        pwd = "11111"
        dsn = cx_Oracle.makedsn('127.0.0.1', '1521', 'TESTDB')
        db = cx_Oracle.connect(username, pwd, dsn)
    return db


def SelectDB(db, sql):
    ##select 查询
    cursor = db.cursor()
    cursor.execute(sql)
    result = cursor.fetchall()
    cursor.close()
    return result


def DMLDB_N(db, sql):
    ##插入，更新，删除
    cursor = db.cursor()
    cursor.execute(sql)
    cursor.close()
    db.commit()


def DMLDB_P(db, sql, para):
    ##插入，更新，删除
    cursor = db.cursor()
    cursor.execute(sql, para)
    cursor.close()
    db.commit()


def DDLDB(db, sql):
    # DDL 语句
    cursor = db.cursor()
    cursor.execute(sql)
    cursor.close()


def printResult(rs):
    for row in rs:
        print(row)





# print("===============向表中插入数据===============")
#
# inst = 'insert into VARIABLE values(10,2,2,3,5)'
# DMLDB_N(db, inst)
# sel = 'select * from CONTROLSYSTEM'
# rs = SelectDB(db, sel)
# printResult(rs)
#
# # print("=======使用参数,向pytb插入数据=============")
# # dt = [{'id': 1, 'val': 'qilong'},
# #       {'id': 2, 'val': 'xxq'},
# #       {'id': 3, 'val': 'aliyun'},
# #       {'id': 4, 'val': 'aliyundba'},
# #       {'id': 5, 'val': 'aliyunsa'},
# #       {'id': 6, 'val': 'aliyunidc'},
# #       {'id': 7, 'val': 'aliyunnework'},
# #       {'id': 8, 'val': 'alibaba'},
# #       {'id': 9, 'val': 'taobao'},
# #       {'id': 10, 'val': 'alipay'},
# #       {'id': 11, 'val': 'tech'},
# #       {'id': 12, 'val': 'oracle'},
# #       {'id': 13, 'val': 'IBM'}
# #       ]
# # inst = 'insert into pytab values(:id,:val)'
# # for bulk in dt:
# #     DMLDB_P(db, inst, bulk)
# # sel = 'select * from pytab'
# # rs = SelectDB(db, sel)
# # printResult(rs)
#
#
# print("===============删除表数据==============")
#
# delt = 'delete from VARIABLE where id=10'
# DMLDB_N(db, delt)
#
#
#
# print("===============查询表数据==============")
#
# sel = 'select * from CONTROLSYSTEM,VARIABLE,TYPE where CONTROLSYSTEM.ID=VARIABLE.CONTROLSYSTEM_ID and VARIABLE.TYPE_ID=TYPE.ID'
# # sel = 'select * from CONTROLSYSTEM'
# rs = SelectDB(db, sel)
#
# printResult(rs)